package com.ismartv.sale.dao;

import com.ismartv.sale.bean.ActiveBean;
import com.ismartv.sale.bean.SaleinBean;
import org.apache.commons.lang.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

public class DaoManager {

	private Connection mysqlcon = null;

	private StringBuffer insertsql = new StringBuffer();
	private StringBuffer insertactivesql = new StringBuffer();
	// private StringBuffer queryonlinekeywords = new StringBuffer();
	private ArrayList<SaleinBean> beanlist = new ArrayList<SaleinBean>();
	private ArrayList<String> onlineKeywords = new ArrayList<String>();
	private StringBuffer offlinequery = new StringBuffer();

	public DaoManager() {
		initMysqlDb();
		String columns = "exchangeorderno,saleorderno,receiveunit,deliverport,destinationport,logisticsexchangetime,exchangegime,tvmode,productno,productname,outcount,logisticsname,transporttype,outtype,sn,receiveaddress,receivecontact,province,orderaddress,proxynumber,consumertype,saleregion,saleprovince,createId,isonlineflag";
		insertsql.append("insert into salein (");
		insertsql.append(columns).append(
				") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		String activecolumns = "sn,province,city,acitveday,device,size";
		insertactivesql.append("insert into active (");
		insertactivesql.append(activecolumns).append(") values (?,?,?,?,?,?)");
		offlinequery
				.append("select saleregion,saleprovince,active.acitveday,count(*) from salein,active where salein.sn = active.sn and !salein.isonlineflag and active.acitveday between ? and ? group by saleregion,saleprovince");
		// queryonlinekeywords.append("select keywords from salechanneltype");
	}

	private void initMysqlDb() {
		try {
//			Class.forName("com.mysql.jdbc.Driver");
//			mysqlcon = DriverManager
//					.getConnection(
//							"jdbc:mysql://localhost:3306/saillog?Unicode=true&characterEncoding=UTF-8",
//							"root", "ba19841006");
			mysqlcon = ConnectionManager.getConnection();
			fillOnlineKeyWords();
		} catch (Exception e) {
			System.out.println("数据库连接失败" + e.getMessage());
		}
	}

	private void insertSaleinData(ArrayList<SaleinBean> datalist)
			throws SQLException {
		// if (checkDataExist(data.getSn()))
		// return;
		// if (patchconut == 1) {
		mysqlcon.setAutoCommit(false);
		PreparedStatement perstmt = mysqlcon.prepareStatement(insertsql
				.toString());
		// }
		for (int i = 0; i < datalist.size(); i++) {
			SaleinBean data = datalist.get(i);
			addOnlineFlag(data);
			if (StringUtils.isEmpty(data.getSn()))
				continue;
			perstmt.setString(1, data.getExchangeOrderNo());
			perstmt.setString(2, data.getSaleorderNumber());
			perstmt.setString(3, data.getReceiveunit());
			perstmt.setString(4, data.getDeliverport());
			perstmt.setString(5, data.getDestinationport());
			perstmt.setString(6, data.getLogisticDeliveryTime());
			perstmt.setString(7, data.getTransitionTime());
			perstmt.setString(8, data.getTvmode());
			perstmt.setString(9, data.getProductNo());
			perstmt.setString(10, data.getProductName());
			perstmt.setString(11, data.getOutcount());
			perstmt.setString(12, data.getLogisticsName());
			perstmt.setString(13, data.getTransportType());
			perstmt.setString(14, data.getOutType());
			perstmt.setString(15, data.getSn());
			perstmt.setString(16, data.getReceiveaddress());
			perstmt.setString(17, data.getReceivecontact());
			perstmt.setString(18, data.getProvince());
			perstmt.setString(19, data.getOrderAddress());
			perstmt.setString(20, data.getProxyNumber());
			perstmt.setString(21, data.getConsumerType());
			perstmt.setString(22, data.getSaleregion());
			perstmt.setString(23, data.getSaleprovince());
			perstmt.setString(23, data.getSaleprovince());
			perstmt.setInt(24, data.getCreateId());
			perstmt.setBoolean(25, data.isIsonlinechanne());
			perstmt.addBatch();
		}
		perstmt.executeBatch();
		mysqlcon.commit();
		mysqlcon.setAutoCommit(true);
		datalist.clear();
		// perstmt.close();
		// perstmt.executeUpdate();
		// perstmt.close();
	}

	public boolean checkDataExist(String sn) throws SQLException {
		StringBuffer query = new StringBuffer();
		query.append("select * from salein where sn = ?");
		PreparedStatement ps = mysqlcon.prepareStatement(query.toString());
		ps.setString(1, sn);
		ResultSet rs = ps.executeQuery();
		if (rs.next())
			return true;
		else
			return false;
	}

	public void addBean(SaleinBean data) {
		if (beanlist.size() > 1000) {
			try {
				insertSaleinData(beanlist);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			beanlist = new ArrayList<SaleinBean>();
		}
		beanlist.add(data);
	}

	public void insertActiveData(ArrayList<ActiveBean> datalist)
			throws SQLException {
		mysqlcon.setAutoCommit(false);
		PreparedStatement perstmt = mysqlcon.prepareStatement(insertactivesql
				.toString());
		for (int i = 0; i < datalist.size(); i++) {
			ActiveBean data = datalist.get(i);
			perstmt.setString(1, data.getSn());
			perstmt.setString(2, data.getProvince());
			perstmt.setString(3, data.getCity());
			perstmt.setString(4, data.getDay());
			perstmt.setString(5, data.getDevice());
			perstmt.setString(6, data.getSize());
			perstmt.addBatch();
		}
		perstmt.executeBatch();
		mysqlcon.commit();
		mysqlcon.setAutoCommit(true);
		datalist.clear();
		// perstmt.close();
		// perstmt.executeUpdate();
		// perstmt.close();
	}

	public void forceInsert() {
		if (beanlist != null && beanlist.size() > 0) {
			try {
				insertSaleinData(beanlist);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private void fillOnlineKeyWords() throws SQLException {
		PreparedStatement perstmt = mysqlcon
				.prepareStatement("select keywords from salechanneltype");
		ResultSet resultset = perstmt.executeQuery();
		while (resultset.next()) {
			onlineKeywords.add(resultset.getString(1));
		}
	}

	private void addOnlineFlag(SaleinBean bean) {
		for (int i = 0; i < onlineKeywords.size(); i++) {
			String element = onlineKeywords.get(i);
			if (bean.getOrderAddress() != null
					&& bean.getOrderAddress().contains(element)) {
				bean.setIsonlinechanne(true);
				break;
			}
		}
	}

	public HashMap<String, Integer> getOfflineData(String startDate,String endDate) {
		PreparedStatement perstmt;
		HashMap<String, Integer> reportData = null;
		try {
			perstmt = mysqlcon.prepareStatement(offlinequery.toString());
			perstmt.setString(1, startDate);
			perstmt.setString(2, endDate);
			ResultSet resultset = perstmt.executeQuery();
			reportData = new HashMap<String, Integer>();
			while (resultset.next()) {
				// onlineKeywords.add(resultset.getString(1));
				reportData.put(resultset.getString(2), resultset.getInt(4));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return reportData;
	}
}
